﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace CJGL
{
   public static class SqlDAL
    {

        public static string ReplenishAllCodes()
        {
            var apiService = new ExternalApiService();
            var codeTypes = new[]
            {
        new { TableName = "code_table_lv1", ProductCode = "8366",nums=10000,chencknum=5000 }, // 小码
        new { TableName = "code_table_lv2", ProductCode = "8367" ,nums=5000 ,chencknum=2500}, // 中码
        new { TableName = "code_table_lv3", ProductCode = "8368" ,nums=2000 ,chencknum=1000}  // 大码
    };

            foreach (var codeType in codeTypes)
            {
                // 查询当前码数量
                string sqlCount = $"SELECT COUNT(1) FROM {codeType.TableName} WHERE is_use=0";
                int count = 0;
                try
                {
                    object result = SqlDbHelper.ExecuteScalar(sqlCount);
                    count = result == null ? 0 : Convert.ToInt32(result);
                }
                catch { count = 0; }

                if (count < codeType.chencknum)
                {
                    // 生成码
                    var genResult = apiService.GenerateCodeBatch(codeType.nums, codeType.ProductCode);
                    if (genResult == null || !genResult.success)
                        return $"生成{codeType.TableName}失败: {genResult?.message ?? "未知错误"}";

                    // 下载码
                    var downloadResult = apiService.DownloadCodeJson(genResult.details.code_batch_id.ToString());
                    if (downloadResult == null || !downloadResult.success || downloadResult.details == null)
                        return $"下载{codeType.TableName}失败: {downloadResult?.message ?? "未知错误"}";

                    // 构造DataTable
                    DataTable dt = new DataTable();
                    dt.Columns.Add("code_str", typeof(string));
                    dt.Columns.Add("is_use", typeof(int));
                    foreach (var item in downloadResult.details)
                    {
                        string code = item.rowData.Split(',')[0];
                        var row = dt.NewRow();
                        row["code_str"] = code;
                        row["is_use"] = 0;
                        dt.Rows.Add(row);
                    }

                    // 批量插入
                    string oldTable = SqlDbHelper.connectionString;
                    try
                    {
                        SqlDbHelper.OpenConn();
                        using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(SqlDbHelper.connectionString))
                        {
                            bulkCopy.DestinationTableName = codeType.TableName;
                            bulkCopy.BatchSize = dt.Rows.Count;
                            bulkCopy.BulkCopyTimeout = 300;
                            bulkCopy.WriteToServer(dt);
                        }
                    }
                    catch (Exception ex)
                    {
                        return $"批量插入{codeType.TableName}失败: {ex.Message}";
                    }
                }
            }
            return "";
        }

        /// <summary>
        /// 获取剩余中码数量
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static String GetHospitalList()
        {
            string sql = @"select top 1 code_str from code_table_lv1 where is_use=0";

            SqlParameter[] parList = {
           
               };
            object tab = SqlDbHelper.ExecuteScalar(sql, parList);
            if (tab==null)
            {
                return "";
            }
            return tab.ToString();
           
        }

        /// <summary>
        /// 根据用户ID获取卡券
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static DataTable GetBarCode()
        {
            string sql = @"select top 1 * from table_codes where is_user=0 order by id ";

            SqlParameter[] parList = {

               };
            DataTable tab = SqlDbHelper.ExecuteDataTable(sql, parList);
            if (tab != null)
            {
                return tab;
            }
            return null;

        }
        /// <summary>
        /// 根据用户ID获取卡券
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static int UpdateBarcode(int id)
        {
            string sql = @"update table_codes set is_user=1 where id="+id;

            int tab = SqlDbHelper.ExecuteNonQuery(sql);
            return tab;

        }
        /// <summary>
        /// 根据用户ID获取卡券
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static int DeleteBarcode(string code)
        {
            string sql = @"delete from  table_codes where code_str='" + code+"'";

            int tab = SqlDbHelper.ExecuteNonQuery(sql);
            return tab;

        }
        // 获取未使用码
        public static DataTable GetUnusedCode(string tableName)
        {
            string sql = $"SELECT TOP 1 code_str FROM {tableName} WHERE is_use=0 order by code_str asc";
            return SqlDbHelper.ExecuteDataTable(sql);
        }
        // 标记码为已用
        public static void SetCodeUsed(string tableName, string code)
        {
            string sql = $"UPDATE {tableName} SET is_use=1 WHERE code_str='{code}'";
            SqlDbHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 插入日志记录（参数化查询，防SQL注入）
        /// </summary>
        /// <param name="parStr">参数字符串</param>
        /// <param name="boxCode">箱码</param>
        /// <param name="errStr">错误信息</param>
        /// <param name="isOk">是否成功（1成功，0失败）</param>
        /// <returns>影响的行数</returns>
        public static int InsertLog(string parStr, string boxCode, string errStr, int isOk)
        {
            if (boxCode == "")
            {
                return 1;
            }

            string sql = @"
        INSERT INTO log_table (par_str, box_code, err_str, is_ok, create_time,do_count) 
        VALUES (
            @ParStr, @BoxCode, @ErrStr, @IsOk, GETDATE(),0
        )";

            // 假设你的SqlDbHelper支持参数化查询
            var parameters = new SqlParameter[]
            {
        new SqlParameter("@ParStr", SqlDbType.Text) { Value = (object)parStr ?? DBNull.Value },
        new SqlParameter("@BoxCode", SqlDbType.VarChar, 50) { Value = (object)boxCode ?? DBNull.Value },
        new SqlParameter("@ErrStr", SqlDbType.Text) { Value = (object)errStr ?? DBNull.Value },
        new SqlParameter("@IsOk", SqlDbType.Int) { Value = isOk }
            };

            return SqlDbHelper.ExecuteNonQuery(sql, parameters);
        }
        // 获取需要重试的记录（do_count < 3 的出错记录）
        public static DataTable GetRetryLog()
        {
            string sql = @"SELECT TOP 1 id, par_str, box_code, err_str, do_count 
                   FROM log_table 
                   WHERE is_ok = 1 AND do_count < 3 
                   ORDER BY create_time ASC";
            return SqlDbHelper.ExecuteDataTable(sql);
        }

        // 重试成功：更新为成功状态，do_count+1
        public static int UpdateRetrySuccess(int logId)
        {
            string sql = @"UPDATE log_table 
                   SET is_ok = 2, do_count = do_count + 1 
                   WHERE id = @LogId";

            var parameters = new SqlParameter[]
            {
        new SqlParameter("@LogId", SqlDbType.Int) { Value = logId }
            };

            return SqlDbHelper.ExecuteNonQuery(sql, parameters);
        }

        // 重试失败：更新错误信息和重试次数
        public static int UpdateRetryFail(int logId)
        {
            string sql = @"UPDATE log_table 
                   SET  do_count = do_count + 1
                   WHERE id = @LogId";

       

            var parameters = new SqlParameter[]
            {
        new SqlParameter("@LogId", SqlDbType.Int) { Value = logId }
            };

            return SqlDbHelper.ExecuteNonQuery(sql, parameters);
        }
    }


}
